﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Xwltz.Framework.Framework;
using Xwltz.Framework.Helper;

namespace Xwltz.Framework.Database
{
    /// <summary>
    /// 简单封装DbHelper
    /// </summary>
    public class SqlServerDbHelper : DataBaseHelper
    {
        /// <summary>
        /// 默认数据库对象
        /// </summary>
        private SqlConnection DefCon { get; set; }

        /// <summary>
        /// 初始化事务
        /// </summary>
        private SqlTransaction _transaction;

        /// <summary>
        /// 创建数据库链接 
        /// 使用config数据库连接字符串
        /// </summary>
        public SqlServerDbHelper()
        {
            var connection = new SqlConnection {ConnectionString = FrameworkConfigs.ConnectionString};
            connection.Open();
            DefCon = connection;
        }

        /// <summary>
        /// 创建数据库链接
        /// 自定义数据库连接字符串
        /// </summary>
        /// <param name="sqlstr"></param>
        public SqlServerDbHelper(string sqlstr)
        {
            var connection = new SqlConnection { ConnectionString = sqlstr };
            connection.Open();
            DefCon = connection;
        }

        /// <summary>
        /// 销毁DB
        /// </summary>
        ~SqlServerDbHelper()
        {
            CloseAndDispose();
        }

        /// <summary>
        /// 创建命令
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="isProc"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        private DbCommand CreateCommand(string sqlstr, bool isProc = false, params DbParameter[] args)
        {
            DbCommand dbCommand = new SqlCommand(sqlstr);
            if (isProc)
            {
                dbCommand.CommandType = CommandType.StoredProcedure;
            }
            foreach (var dbParameter in args)
            {
                dbCommand.Parameters.Add(dbParameter);
            }
            return dbCommand;
        }

        /// <summary>
        /// 执行命令
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="closeCon"></param>
        /// <returns></returns>
        private int ExcuteNonQuery(DbCommand cmd, bool closeCon = true)
        {
            int result;
            try
            {
                cmd.Connection = DefCon;
                if (cmd.Connection.State == ConnectionState.Closed)
                {
                    cmd.Connection.Open();
                }
                if (_transaction != null)
                {
                    cmd.Transaction = _transaction;
                }
                result = cmd.ExecuteNonQuery();
            }
            finally
            {
                if (closeCon && _transaction == null)
                {
                    cmd.Connection.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// 执行SQL语句返回成功条数
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="isProc"></param>
        /// <param name="closeCon"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public int ExcuteNonQuery(string sqlstr, bool isProc = false, bool closeCon = true, params DbParameter[] args)
        {
            var cmd = CreateCommand(sqlstr, isProc, args);
            return ExcuteNonQuery(cmd, closeCon);
        }

        /// <summary>
        /// 执行命令返回首行首列
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="closeCon"></param>
        /// <returns></returns>
        private object ExcuteScalar(DbCommand cmd, bool closeCon = true)
        {
            object result;
            try
            {
                cmd.Connection = DefCon;
                if (cmd.Connection.State == ConnectionState.Closed)
                {
                    cmd.Connection.Open();
                }
                if (_transaction != null)
                {
                    cmd.Transaction = _transaction;
                }
                result = cmd.ExecuteScalar();
            }
            finally
            {
                if (closeCon && _transaction == null)
                {
                    cmd.Connection.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// 执行SQL语句返回首行首列
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="isProc"></param>
        /// <param name="closeCon"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public object ExcuteScalar(string sqlstr, bool isProc = false, bool closeCon = true, params DbParameter[] args)
        {
            var cmd = CreateCommand(sqlstr, isProc, args);
            return ExcuteScalar(cmd, closeCon);
        }

        /// <summary>
        /// 执行命令返回DataTable
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        private DataTable ExcuteTable(DbCommand cmd)
        {
            if (cmd.Connection == null)
            {
                cmd.Connection = DefCon;
            }
            if (_transaction != null)
            {
                cmd.Transaction = _transaction;
            }
            var sqlDataAdapter = new SqlDataAdapter((SqlCommand)cmd);
            var dataSet = new DataSet();
            sqlDataAdapter.Fill(dataSet);
            return dataSet.Tables[0];
        }

        /// <summary>
        /// 执行SQL语句返回DataTable
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="isProc"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public DataTable ExcuteTable(string sqlstr, bool isProc = false, params DbParameter[] args)
        {
            var cmd = CreateCommand(sqlstr, isProc, args);
            return ExcuteTable(cmd);
        }

        /// <summary>
        /// 执行命令返回DataReader
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="con"></param>
        /// <returns></returns>
        private DbDataReader ExcuteReader(DbCommand cmd)
        {
            cmd.Connection = DefCon;
            DbDataReader result;
            try
            {
                if (DefCon.State != ConnectionState.Open)
                {
                    DefCon.Open();
                }

                result = cmd.ExecuteReader();
            }
            catch
            {
                result = null;
            }
            finally
            {
                CloseAndDispose();
            }
            return result;
        }

        /// <summary>
        /// 执行SQL语句返回DataReader
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="con"></param>
        /// <param name="isProc"></param>
        /// <returns></returns>
        public DbDataReader ExcuteReader(string sqlstr, bool isProc = false, params DbParameter[] args)
        {
            var cmd = CreateCommand(sqlstr, isProc, args);
            return ExcuteReader(cmd);
        }

        /// <summary>
        /// 开启事务
        /// </summary>
        /// <param name="openCon"></param>
        public void BeginTran(bool openCon = true)
        {
            if (openCon && DefCon.State != ConnectionState.Open)
            {
                DefCon.Open();
            }
            _transaction = DefCon.BeginTransaction();
        }
       
        /// <summary>
        /// 提交事务
        /// </summary>
        /// <param name="closeCon"></param>
        public void TranCommit(bool closeCon = true)
        {
            if (_transaction == null)
            {
                return;
            }
            try
            {
                _transaction.Commit();
            }
            catch (Exception ex)
            {
                _transaction.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                _transaction.Dispose();
                if (closeCon && DefCon.State == ConnectionState.Open)
                {
                    DefCon.Close();
                }
                _transaction = null;
            }
        }
        
        /// <summary>
        /// 回滚事务
        /// </summary>
        /// <param name="closeCon"></param>
        public void TranRollBack(bool closeCon = true)
        {
            if (_transaction == null)
            {
                return;
            }
            try
            {
                _transaction.Rollback();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                _transaction.Dispose();
                if (closeCon && DefCon.State == ConnectionState.Open)
                {
                    DefCon.Close();
                }
                _transaction = null;
            }
        }

        /// <summary>
        /// 根据表名获取表字段信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<TableColumns> GetTableColumns(string tableName)
        {
            var strSql = $"SELECT a.name AS 'Name', b.name AS 'Type', COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 'Size', (CASE WHEN (SELECT COUNT(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid  FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK')) > 0 THEN 'PrimaryKey' else '' end) AS 'PrimaryKey', (CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN 'Identity' else '' end) AS 'Identity', (CASE WHEN a.isnullable = 1 THEN 'true' else 'false' end) AS 'IsNull', isnull(g.[value],'') AS 'Remark' FROM syscolumns a LEFT JOIN systypes b ON a.xtype = b.xusertype inner JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id WHERE d.name = '{tableName}' ORDER BY a.colorder  ";
            List<TableColumns> tableColumns = new List<TableColumns>();
            var dr = ExcuteReader(strSql);
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    var column = new TableColumns();
                    column.Name = ExecToString(dr, "Name");//字段名
                    column.Type = ExecToString(dr, "Type");//类型
                    column.Size = ExecToString(dr, "Size");//长度
                    column.PrimaryKey = ExecToString(dr, "PrimaryKey");//是否是主键
                    column.Identity = ExecToString(dr, "Identity");//是否是标识列
                    column.IsNull = ExecToString(dr, "IsNull");//是否允许为空
                    column.Remark = ExecToString(dr, "Remark");//注释
                    tableColumns.Add(column);
                }
            }

            dr.Close();
            return tableColumns;
        } 

        /// <summary>
        /// 生成条件SQL语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        private string CreateSelectWhereSql(string tableName, Dictionary<object, object> obj)
        {
            if (tableName.IsNullOrEmpty()) return "";

            var executeSql = $"select * from {tableName}";
            if (obj == null) return executeSql;

            var whereStr = " where 1=1 ";
            foreach (var str in obj)
            {
                var type = str.Value.GetType();
                if (type == typeof(int))
                {
                    whereStr += " and " + str.Key + " = " + str.Value;
                }
                else if (type == typeof(bool))
                {
                    whereStr += " and " + str.Key + " = '" + str.Value.ToString().ToBool().ToBoolInt() + "'";
                }
                else
                {
                    whereStr += " and " + str.Key + " = '" + str.Value + "'";
                }
            }
            executeSql += whereStr;
            return executeSql;
        }

        /// <summary>
        /// 关闭并释放数据库连接
        /// </summary>
        public void CloseAndDispose()
        {
            if (DefCon.State == ConnectionState.Open)
            {
                DefCon.Close();
                DefCon.Dispose();
            }

            _transaction?.Dispose();
        }
    }
}
